package cn.edu.gzgs.service.impl;


import cn.edu.gzgs.entity.ListMeta;
import cn.edu.gzgs.entity.Project;
import cn.edu.gzgs.mapper.ListMetaMapper;
import cn.edu.gzgs.mapper.ProjectMapper;
import cn.edu.gzgs.service.ProjectService;
import cn.edu.gzgs.vo.DeclareResponse;
import cn.edu.gzgs.vo.ListDetailResponse;
import cn.edu.gzgs.vo.ProjectTreeVO;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Collectors;

@Service
@Slf4j
public class ProjectServicelmpl extends ServiceImpl<ProjectMapper, Project> implements ProjectService {

    @Autowired
    private ProjectMapper projectMapper;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private ListMetaMapper listMetaMapper;

    @Override
    public List<ProjectTreeVO> getProjectTree() {
        // 1. 获取原始数据
        List<ProjectMapper.ProjectTreeRaw> rawData = projectMapper.getProjectTreeRaw();

        // 2. 构建树形结构
        Map<String, ProjectTreeVO> projectMap = new LinkedHashMap<>();
        List<ProjectTreeVO> result = new ArrayList<>();

        // 第一遍：创建项目节点
        for (ProjectMapper.ProjectTreeRaw item : rawData) {
            String projectId = item.getProjectId();

            if (!projectMap.containsKey(projectId)) {
                ProjectTreeVO projectNode = new ProjectTreeVO();
                projectNode.setId(projectId);
                projectNode.setName(item.getProjectName());
                projectNode.setType("project"); // 项目节点类型
                projectNode.setChildren(new ArrayList<>());

                projectMap.put(projectId, projectNode);
                result.add(projectNode);
            }
        }

        // 第二遍：添加名单作为子节点
        for (ProjectMapper.ProjectTreeRaw item : rawData) {
            String projectId = item.getProjectId();
            ProjectTreeVO projectNode = projectMap.get(projectId);

            if (item.getListId() != null && projectNode != null) {
                ProjectTreeVO listNode = new ProjectTreeVO();
                listNode.setId(item.getListId());
                listNode.setName(item.getListName());
                listNode.setType("list"); // 名单节点类型
                listNode.setChildren(new ArrayList<>()); // 空子节点

                projectNode.getChildren().add(listNode);
            }
        }

        // 3. 记录日志
        log.info("构建项目树完成，共 {} 个项目，{} 个名单",
                result.size(),
                result.stream().mapToInt(p -> p.getChildren().size()).sum());
        return result;
    }

    @Override
    public ListDetailResponse getListDetail(String listId) throws Exception {
        // 生成表名
        String tableName = "list_data_" + listId.replace(".", "_");

        // 获取列信息
        List<String> columns = getTableColumns(tableName);

        // 获取数据
        List<Map<String, Object>> records = getTableData(tableName);

        // 返回响应
        return new ListDetailResponse(columns, records);
    }

    private List<String> getTableColumns(String tableName) {
        String sql = "SHOW COLUMNS FROM `" + tableName + "`";

        try {
            List<Map<String, Object>> columns = jdbcTemplate.queryForList(sql);
            return columns.stream()
                    .map(col -> (String) col.get("Field"))
                    .filter(field -> !"id".equals(field)) // 排除自增主键
                    .collect(Collectors.toList());
        } catch (BadSqlGrammarException e) {
            throw new TableNotFoundException("表不存在: " + tableName);
        }
    }

    private List<Map<String, Object>> getTableData(String tableName) {
        String sql = "SELECT * FROM `" + tableName + "`";

        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);

        // 移除自增主键列
        return rows.stream()
                .map(row -> {
                    Map<String, Object> cleaned = new LinkedHashMap<>(row);
                    cleaned.remove("id");
                    return cleaned;
                })
                .collect(Collectors.toList());
    }

    @Override
    public List<Map<String, Object>> getAllProjects() {
        // 查询项目表，只返回 id 和 name 字段
        return projectMapper.selectMaps(
                new QueryWrapper<Project>()
                        .select("id", "name")
                        .orderByAsc("id")
        );
    }

    @Override
    public List<Map<String, Object>> getListMetaByLeader(String leaderId, String projectId) {
        // 构建查询条件
        QueryWrapper<ListMeta> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id", "project_id", "name", "description", "status")
                .eq("leader_id", leaderId);

        // 添加可选的项目ID条件
        if (projectId != null && !projectId.isEmpty()) {
            queryWrapper.eq("project_id", projectId);
        }

        // 执行查询
        return listMetaMapper.selectMaps(queryWrapper);
    }

    @Override
    public DeclareResponse declareList(
            String leaderId,
            String projectId,
            String tableName,
            List<String> columns,
            List<Map<String, Object>> data
    ) throws Exception {
        // 步骤1：生成新的 list_id
        String newListId = generateNewListId(projectId);

        // 步骤2：插入 list_meta
        ListMeta listMeta = new ListMeta();
        listMeta.setId(newListId);
        listMeta.setProjectId(projectId);
        listMeta.setName(tableName);
        listMeta.setStatus("pending");
        listMeta.setLeaderId(leaderId);
        listMetaMapper.insert(listMeta);

        // 步骤3：创建数据表
        String tableNameDb = "list_data_" + newListId.replace(".", "_");
        createDynamicTable(tableNameDb, columns);

        // 步骤4：批量插入数据
        if (data != null && !data.isEmpty()) {
            batchInsertData(tableNameDb, columns, data);
        }

        return new DeclareResponse(newListId);
    }

    /**
     * 生成新的名单ID
     */
    private String generateNewListId(String projectId) {
        // 查询当前项目下最后一个名单ID
        QueryWrapper<ListMeta> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id")
                .eq("project_id", projectId)
                .orderByDesc("id")
                .last("LIMIT 1");

        ListMeta lastMeta = listMetaMapper.selectOne(queryWrapper);

        int seq = 1;
        if (lastMeta != null) {
            // 解析最后一个ID的序号部分
            String[] parts = lastMeta.getId().split("\\.");
            if (parts.length > 0) {
                String lastSeqStr = parts[parts.length - 1];
                try {
                    seq = Integer.parseInt(lastSeqStr) + 1;
                } catch (NumberFormatException e) {
                    log.warn("无法解析序号: {}", lastSeqStr);
                }
            }
        }

        // 格式化序号为3位数字
        String newSeqStr = String.format("%03d", seq);
        return projectId + "." + newSeqStr;
    }

    /**
     * 创建动态表
     */
    private void createDynamicTable(String tableName, List<String> columns) {
        // 构建列定义
        StringBuilder sql = new StringBuilder("CREATE TABLE `")
                .append(tableName)
                .append("` (`id` INT NOT NULL AUTO_INCREMENT");

        for (String column : columns) {
            sql.append(", `").append(column).append("` VARCHAR(255) NULL");
        }

        sql.append(", PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        // 执行DDL
        jdbcTemplate.execute(sql.toString());
        log.info("创建表成功: {}", tableName);
    }

    /**
     * 批量插入数据
     */
    private void batchInsertData(String tableName, List<String> columns, List<Map<String, Object>> data) {
        // 构建列名部分
        String columnNames = columns.stream()
                .map(col -> "`" + col + "`")
                .collect(Collectors.joining(", "));

        // 构建占位符
        String placeholders = columns.stream()
                .map(col -> "?")
                .collect(Collectors.joining(", "));

        // 构建SQL
        String sql = "INSERT INTO `" + tableName + "` (" + columnNames + ") VALUES (" + placeholders + ")";

        // 准备参数
        List<Object[]> params = new ArrayList<>();
        for (Map<String, Object> row : data) {
            Object[] rowValues = new Object[columns.size()];
            for (int i = 0; i < columns.size(); i++) {
                rowValues[i] = row.getOrDefault(columns.get(i), null);
            }
            params.add(rowValues);
        }

        // 批量执行
        jdbcTemplate.batchUpdate(sql, params);
        log.info("插入 {} 条数据到表: {}", data.size(), tableName);
    }

    // 自定义异常
    public static class TableNotFoundException extends RuntimeException {
        public TableNotFoundException(String message) {
            super(message);
        }
    }
}